package com.cn.dao;

import com.cn.po.WordWL;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface WangLuWordDao {
    @Insert("insert into WangLuWords (id,english_word,us_phonetic,us_voice,uk_phonetic,uk_voice,you_dao_meaning,wang_lu_meaning,wang_lu_chapter,remark,errorCount) values (#{id},#{word},#{usPhonetic},#{usVoice},#{ukPhonetic},#{ukVoice},#{youDaoMeaning},#{wangLuMeaning},#{wangLuChapter},#{remark},#{errorCount})")
    public int save(WordWL wordWL);

    @Update("update WangLuWords set id=#{id},english_word=#{word},us_phonetic=#{usPhonetic},us_voice=#{usVoice},uk_phonetic=#{ukPhonetic},uk_voice=#{ukVocie},you_dao_meaning=#{youDaoMeaning},wang_lu_meaning=#{wangLuMeaning},wang_lu_chapter=#{wangLuChapter},remark=#{remark},errorCount=#{errorCount}")
    public int update(WordWL wordWL);
    @Delete("delete from WangLuWords where id=#{id}")
    public int deleteById(Integer id);

    @Select("select * from WangLuWords where id = #{id}")
    @Results(id = "wangLuWordResultMap",value = {
            @Result(property = "word", column = "english_word"),
            @Result(property = "usPhonetic",column = "us_phonetic"),
            @Result(property = "usVoice",column = "us_voice"),
            @Result(property = "ukPhonetic",column = "uk_phonetic"),
            @Result(property = "ukVoice",column = "uk_voice"),
            @Result(property = "youDaoMeaning",column = "you_dao_meaning"),
            @Result(property = "wangLuMeaning",column = "wangLuMeaning"),
            @Result(property = "wangLuChapter",column = "wangLuChapter")
    })
    public WordWL getWordById(Integer id);

    @Select("select * from WangLuWords where english_word = #{word}")
    @ResultMap("wangLuWordResultMap")
    public WordWL getByWord(String word);

    @Select("select * from WangLuWords")
    @ResultMap("wangLuWordResultMap")
    public List<WordWL> getAll();

    // 获取所有不同的章节
    @Select("SELECT DISTINCT wang_lu_chapter FROM WangLuWords ORDER BY wang_lu_chapter")
    List<String> getDistinctChapters();

    // 根据章节和查询条件分页查询单词
    @Select("SELECT * FROM WangLuWords " +
            "WHERE (#{query} IS NULL OR #{query} = '' OR english_word LIKE CONCAT('%', #{query}, '%')) " +
            "AND (#{chapter} IS NULL OR #{chapter} = '' OR wang_lu_chapter = #{chapter}) " +
            "ORDER BY id " +
            "LIMIT #{limit} OFFSET #{offset}")
    List<WordWL> getWordsByPage(
            @Param("query") String query,
            @Param("chapter") String chapter,
            @Param("offset") int offset,
            @Param("limit") int limit
    );

    // 统计符合条件的单词总数
    @Select("SELECT COUNT(*) FROM WangLuWords " +
            "WHERE (#{query} IS NULL OR #{query} = '' OR english_word LIKE CONCAT('%', #{query}, '%')) " +
            "AND (#{chapter} IS NULL OR #{chapter} = '' OR wang_lu_chapter = #{chapter})")
    int getTotalCount(
            @Param("query") String query,
            @Param("chapter") String chapter
    );

    //  获取指定章节的所有单词
    @Select("select * from WangLuWords where wang_lu_chapter = #{chapter};")
    List<WordWL> getWordsByChapter(@Param("chapter") String chapter);


    //PO不是固定的，根据业务需要什么PO创造响应的PO
    //获取指定页的错误单词
    @Select("SELECT w.id, w.english_word AS word, w.us_phonetic AS usPhonetic, w.us_voice AS usVoice, w.uk_phonetic AS ukPhonetic, w.uk_voice AS ukVoice, w.you_dao_meaning AS youDaoMeaning, w.wang_lu_meaning AS wangLuMeaning,w.wang_lu_chapter As wangLuChapter, w.remark AS remark, e.current_error_count AS errorCount FROM WangLuWords w JOIN word_test_errors e ON w.id = e.word_id where e.current_error_count > 0 ORDER BY e.current_error_count DESC LIMIT #{limit} OFFSET #{offset}")
    List<WordWL> getPaginatedErrorWords(@Param("limit") int limit, @Param("offset") int offset);

    @Select("SELECT COUNT(*) FROM WangLuWords w JOIN word_test_errors e ON w.id = e.word_id")
    int getErrorWordsCount();

    // 更新错误次数
    @Update("UPDATE word_test_errors SET current_error_count = current_error_count - 1 " +
            "WHERE word_id = #{wordId} AND current_error_count > 0")
    void decrementErrorCount(@Param("wordId") int wordId);

    //@Update("INSERT INTO word_test_errors (word_id, current_error_count, history_error_count) " +
    //        "VALUES (#{wordId}, 1, 1) " +
    //        "ON DUPLICATE KEY UPDATE current_error_count = current_error_count + 1, " +
    //        "history_error_count = history_error_count + 1")
    //void incrementErrorCount(@Param("wordId") int wordId);


    // 检查 word_id 是否已经存在于错误表中
    @Select("SELECT COUNT(*) > 0 FROM word_test_errors WHERE word_id = #{wordId}")
    boolean wordExists(@Param("wordId") int wordId);

    // 如果存在该单词的错误记录，更新 current_error_count 和 history_error_count
    @Update("UPDATE word_test_errors SET current_error_count = current_error_count + 1, " +
            "history_error_count = history_error_count + 1 WHERE word_id = #{wordId}")
    void updateErrorCount(@Param("wordId") int wordId);


    @Update("update word_test_errors set current_error_count = current_error_count-1 where word_id = #{wordId}")
    void updateCorrectCount(@Param("wordId") int wordId);


    // 如果没有该单词的错误记录，插入新的记录
    @Insert("INSERT INTO word_test_errors (word_id, current_error_count, history_error_count) " +
            "VALUES (#{wordId}, 1, 1)")
    void insertErrorWord(@Param("wordId") int wordId);




    // 获取所有的错误单词
    @Select("SELECT w.id AS id, w.english_word AS word, w.us_phonetic AS usPhonetic, w.us_voice AS usVoice, w.uk_phonetic AS ukPhonetic, w.uk_voice AS ukVoice,w.you_dao_meaning AS youDaoMeaning, w.wang_lu_meaning AS wangLuMeaning, w.wang_lu_chapter AS wangLuChapter, w.remark AS remark,e.current_error_count AS errorCount FROM WangLuWords w JOIN word_test_errors e ON w.id = e.word_id WHERE e.current_error_count >= 1 ORDER BY w.wang_lu_chapter")
    List<WordWL> findWordsWithErrors();



    @Select("SELECT w.id AS id, w.english_word AS word, w.us_phonetic AS usPhonetic,\n" +
            " w.us_voice AS usVoice, w.uk_phonetic AS ukPhonetic, w.uk_voice AS ukVoice,\n" +
            " w.you_dao_meaning AS youDaoMeaning, w.wang_lu_meaning AS wangLuMeaning, w.wang_lu_chapter AS wangLuChapter,\n" +
            "       w.remark AS remark, e.current_error_count AS errorCount\n" +
            "FROM WangLuWords w join word_test_errors e on w.id = e.word_id where w.wang_lu_chapter = #{chapter} and e.current_error_count >=1 order by e.current_error_count desc")
    List<WordWL> findErrorWordsByChapter(String chapter);

}
